package dao.mysql;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.mysql.jdbc.PreparedStatement;

import model.LeaveTypeDTO;
import dao.DAOException;
import dao.LeaveTypeDAO;

public class LeaveTypeDAOImpl implements LeaveTypeDAO  {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveappforiss";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";

	// Create oneLeaveType instance	
	public LeaveTypeDTO createoneLeaveType() {
        return new LeaveTypeDTO();
  }
	public LeaveTypeDTO findLeaveType(String Name) throws DAOException {
		String selectSql = "SELECT * FROM leavetype WHERE Name='" + Name.trim()
				+ "';";
		LeaveTypeDTO oneLeaveType = loadLeaveType(selectSql);
		System.out.println(oneLeaveType.toString());
		return oneLeaveType;
	}
	
	private LeaveTypeDTO loadLeaveType(String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		LeaveTypeDTO oneLeaveType = createoneLeaveType();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				   oneLeaveType.setLeaveTypeID(result.getInt("LeaveTypeID")); 
	               oneLeaveType.setName(result.getString("Name")); 
	               oneLeaveType.setDuration(result.getDouble("Duration")); 
	              			
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return oneLeaveType;
	}
	
	public ArrayList<LeaveTypeDTO> findAllLeaveType() throws DAOException {
		String selectSql = "SELECT * FROM leavetype;";
		ArrayList<LeaveTypeDTO>  items = loadAllLeaveType(selectSql);
		//System.out.println(oneLeaveType.toString());
		return items;
	}
	
	private ArrayList<LeaveTypeDTO> loadAllLeaveType(String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		ArrayList<LeaveTypeDTO>  items = new ArrayList<LeaveTypeDTO>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				LeaveTypeDTO oneLeaveType = createoneLeaveType();
				   oneLeaveType.setLeaveTypeID(result.getInt("LeaveTypeID")); 
	               oneLeaveType.setName(result.getString("Name")); 
	               oneLeaveType.setDuration(result.getDouble("Duration")); 
	              			items.add(oneLeaveType);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}
	